﻿using System;
using System.Data;
using System.Collections.Generic;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class OrgChat1DAL : IOrgchart1DAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable LoadRoot(string strEmpSeq)
        {
            DataTable result;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@emp_seq", strEmpSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                string strSQL = @"select p.pos_seq as ID, p.pos_name, p.pos_working_upper_seq as parent_ID
                    , CONCAT(px.prefix_name, e.first_name, ' ', e.last_name) AS emp_name, js.job_site_name
                    , et.emp_type_name
                    from hrm_mp_positions p
                    left join hrm_ps_employees e
                    ON p.pos_seq = e.pos_seq
                    LEFT JOIN mas_name_prefixes px
                    ON e.name_prefix_seq = px.name_prefix_seq
                    LEFT JOIN hrm_mp_job_sites js
                    ON e.working_job_site_seq = js.job_site_seq
                    LEFT JOIN hrm_ps_employee_types et
                    ON e.emp_type_seq = et.emp_type_seq
                    where e.emp_seq = @emp_seq
                    and p.record_status <> @status";
                result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }

        public DataTable LoadPositionWorkingJobsites(string strPosSeq)
        {
            DataTable result;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@pos_seq", strPosSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                string strSQL = @"select p.pos_seq as ID, p.pos_name, p.pos_working_upper_seq as parent_ID
                    , CONCAT(px.prefix_name, e.first_name, ' ', e.last_name) AS emp_name, js.job_site_name
                    , et.emp_type_name
                    from hrm_mp_positions p
                    left join hrm_ps_employees e
                    ON p.pos_seq = e.pos_seq
                    LEFT JOIN mas_name_prefixes px
                    ON e.name_prefix_seq = px.name_prefix_seq
                    LEFT JOIN hrm_mp_job_sites js
                    ON e.working_job_site_seq = js.job_site_seq
                    LEFT JOIN hrm_ps_employee_types et
                    ON e.emp_type_seq = et.emp_type_seq
                    where p.pos_working_upper_seq = @pos_seq
                    and p.record_status <> @status";
                result = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }

        public void UpdateWorkingUpper(int Sequence, string WorkingUpper, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@working_upper_seq", WorkingUpper);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_MP_POSITIONS"
                        + " SET POS_WORKING_UPPER_SEQ = @working_upper_seq"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + " WHERE POS_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception error)
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

     }
}